import pandas as pd
from flask import Flask, render_template, request
import pymysql

app = Flask(__name__)


def insert_mysql(sql):
    db = pymysql.connect(host="localhost", user="root", password="123456", database="project_01")
    cursor = db.cursor()
    try:
        cursor.execute(sql)
        db.commit()
        print('successful')
    except:
        import traceback
        traceback.print_exc()
        print("Error: unable to fetch data")
    db.close()


def select_mysql(sql):
    db = pymysql.connect(host="localhost", user="root", password="123456", database="project_01")
    cursor = db.cursor()
    try:
        cursor.execute(sql)
        # 只能有一个cursor.fetchall()
        result = cursor.fetchall()
        if result == ():
            return 'null'
        else:
            new_result = result[0][0]
            return new_result

    except:
        import traceback
        traceback.print_exc()
        print("Error: unable to fetch data")
    db.close()


def select_all_mysql(sql):
    db = pymysql.connect(host="localhost", user="root", password="123456", database="project_01")
    cursor = db.cursor()
    try:
        cursor.execute(sql)
        # 只能有一个cursor.fetchall()
        result = cursor.fetchall()
        return result
    except:
        import traceback
        traceback.print_exc()
        print("Error: unable to fetch data")
    db.close()


def add_mysql(sql):
    db = pymysql.connect(host="localhost", user="root", password="123456", database="project_01")
    cursor = db.cursor()
    try:
        cursor.execute(sql)
        # 只能有一个cursor.fetchall()
        result = cursor.fetchall()
        a = []
        for i in result:
            a.append(i[0][1:])
        value = int(max(a)) + 1
        return value
    except:
        import traceback
        traceback.print_exc()
        print("Error: unable to fetch data")
    db.close()


def delete_mysql(sql):
    db = pymysql.connect(host="localhost", user="root", password="123456", database="project_01")
    cursor = db.cursor()
    try:
        cursor.execute(sql)
        db.commit()
        return 'delete success'
    except:
        import traceback
        traceback.print_exc()
        print("Error: unable to fetch data")
    db.close()


def solve_name(str1):
    old_list = str1.split()
    new_list = ' '.join(old_list)
    return new_list


# 查询数据数量
def select_all_quantity(sql):
    db = pymysql.connect(host="localhost", user="root", password="123456", database="project_01")
    cursor = db.cursor()
    try:
        cursor.execute(sql)
        # 只能有一个cursor.fetchall()
        result = cursor.fetchall()
        result = result[0][0]
        return result
    except:
        import traceback
        traceback.print_exc()
        print("Error: unable to fetch data")
    db.close()


def exist_mysql(sql):  # 查询数据是否存在
    db = pymysql.connect(host="localhost", user="root", password="123456", database="project_01")
    cursor = db.cursor()
    try:
        cursor.execute(sql)
        # 只能有一个cursor.fetchall()
        result = cursor.fetchall()

        result = result[0][0]

        if result == int(0):
            return 'not_exist'
        elif result == int(1):
            return 'exist'

    except:
        import traceback
        traceback.print_exc()
        print("Error: unable to fetch data")
        db.close()


@app.route('/')
def index():
    return 'Hello World'


@app.route('/upload', methods=['POST', 'GET'])
def upload():
    if request.method == 'POST':
        data = request.form
        excel_file = request.files['file']
        project = request.form['project_id']
        year = request.form['year_id']
        df = pd.read_excel(excel_file, index_col=0, header=[0, 1, 2])
        df = df.fillna(0)

        insert_quantity = 0  # 本次录入数量
        # 物种名编号
        name = []
        name_01 = []
        for i in df:
            if i[2] == '拉丁名':
                name = df[i].values
        for i in name:
            name_01.append(i)
        for i in range(len(name_01)):
            insert_mysql('insert into side_table(type,name)values("物种","S00")')
            na_id = add_mysql('select name from side_table where type = "物种"')
            des_exist = select_mysql('select name from side_table where des = "%s"' % (name_01[i]))
            if des_exist == "null":
                if na_id <= 9:
                    abc = 'S' + '0' + str(na_id)
                    name_01[i] = solve_name(name_01[i])
                    insert_mysql('insert into side_table(project,type,name,des)values("%s","%s","%s","%s")' % (
                        project, '物种', abc, name_01[i]))
                else:
                    abcd = 'S' + str(na_id)
                    name_01[i] = solve_name(name_01[i])
                    insert_mysql('insert into side_table(project,type,name,des)values("%s","%s","%s","%s")' % (
                        project, '物种', abcd, name_01[i]))
            else:
                print('已存在此物种编号')
        insert_mysql('delete from side_table where name = "S00"')

        # 点位编号
        site_name = []
        site_name_index = []
        for i in df:
            site_name.append(i[0])
        site_name[0:7] = []
        site_name = list(set(site_name))

        for i in range(len(site_name)):
            site_exist = select_mysql('select name from side_table where des = "%s" ' % (site_name[i]))
            if site_exist == "null":
                site_name_index.append('D' + str(site_name[i][-1]))
                aaa = 'D' + str(site_name[i][-1])
                insert_mysql('insert into side_table(project,type,name,des)values("%s","%s","%s","%s")' % (
                    project, '点位', aaa, site_name[i]))
            else:

                print('点位编号已存在')
        b = df.columns.values
        for row in df.index.values:
            data = df.loc[row].values
            insert_mysql('insert into main_table(name,site,month)values("SSS","S","S")')
            for i in range(7, len(data) - 1):
                site = b[i][0]
                time = b[i][1]
                type = b[i][2]
                name = data[6]
                name = solve_name(name)
                name_id = select_mysql('select name from side_table where des="%s"' % (name))
                site_id = select_mysql('select name from side_table where des="%s"' % (site))
                score_exist = exist_mysql(
                    'select exists(select * from main_table where project = "%s" and yearid = "%s" and name = "%s" and site = "%s" and month = "%s")' % (
                        project, year, name_id, site_id, time))

                if type == '定性':
                    if score_exist == "not_exist":
                        value = data[i]
                        value_01 = data[i + 1]
                        print(name, name_id, site, site_id, time, type, value, value_01)
                        insert_mysql(
                            'insert into main_table(project,yearid,name,site,month,qualitative,quantitative)values("%s","%s","%s","%s","%s","%s","%s")' % (
                                project, year, name_id, site_id, time, value, value_01))
                        print('----------')
                        insert_quantity += 1
                    elif score_exist == "exist":
                        value = data[i]
                        value_01 = data[i + 1]
                        delete_mysql(
                            'delete from main_table where project = "%s" and yearid = "%s" and name = "%s" and site = "%s" and month = "%s")' % (
                                project, year, name_id, site_id, time))
                        insert_mysql(
                            'insert into main_table(project,yearid,name,site,month,qualitative,quantitative)values("%s","%s","%s","%s","%s","%s","%s")' % (
                                project, year, name_id, site_id, time, value, value_01))
                        print('已取缔')
                        insert_quantity += 1
            else:
                pass
            insert_mysql('delete from main_table where name = "SSS"')

        site_quantity = select_all_quantity('select count(*) from side_table where type = "点位"')
        species_quantity = select_all_quantity('select count(*) from side_table where type = "物种"')
        record_quantity = select_all_quantity('select count(*) from main_table')

        print(project, insert_quantity, site_quantity, species_quantity, record_quantity)
        a = {
            'project': project,
            'insert_quantity': insert_quantity,
            'site_quantity': site_quantity,
            'species_quantity': species_quantity,
            'record_quantity': record_quantity,
        }
        return a


    else:
        print('sb')
    return render_template('upload_01.html')


@app.route('/look', methods=['POST', 'GET'])
def look():
    if request.method == "POST":
        print(request.form.get('type'))
        if request.form.get('type') == 'a':
            project = request.form.get('project')
            print(project)
            da = {}
            site_id = select_all_mysql('select distinct site from main_table where project = "%s"' % (project))
            month_id = select_all_mysql('select distinct month from main_table where project = "%s"' % (project))
            for month in month_id:
                a = {}

                for site in site_id:
                    score = select_all_mysql(
                        'select count(*) from main_table where quantitative > "0.0" and project = "%s" and site = "%s" and month = "%s"' % (
                            project, site[0], month[0]))
                    if score == ():
                        a[site[0]] = "0"
                        da[month[0]] = a
                    else:
                        a[site[0]] = score[0][0]
                        da[month[0]] = a
            f = {}
            for site in site_id:
                count = select_all_mysql(
                    'select count(*) from main_table where quantitative > "0.0" and project = "%s" and site = "%s"' % (
                        project,
                        site[0]))
                f[site[0]] = count[0][0]
            da['总表'] = f

            print(da)

            return da
        elif request.form.get('type') == 'b':
            project = request.form.get('project')
            ph_name = select_all_mysql(
                'select distinct ph_name from physics_value_table where project="%s"' % (project))
            da = {}
            for i in ph_name:
                daa = {}
                site_num = select_all_mysql(
                    'select site,num from physics_value_table where project="%s" and ph_name = "%s"' % (project, i[0]))

                for t in site_num:
                    daa[t[0]] = t[1]
                da[i[0]] = daa
                print(da)
            return da
        elif request.form.get('type') == 'c':
            project = request.form.get('project')
            import collections
            a = []
            all = []
            all_pd = {}
            ll_pd = {}
            alll_pd = {}
            zs = 0
            sql = 'select name from main_table where project="%s" and quantitative > "0.0"' % (project)
            db = pymysql.connect(host="localhost", user="root", password="123456", database="project_01")
            cursor = db.cursor()
            try:
                cursor.execute(sql)
                result = cursor.fetchall()
                print(result)
                for i in result:
                    print(i[0])
                    all.append(i[0])
                    if i[0] in a:
                        pass
                    else:
                        a.append(i[0])
                max_all = collections.Counter(all)
                for i in max_all:
                    zs += max_all[i]
                    all_pd[i] = max_all[i]
                for i in max_all:
                    yg = (max_all[i] / zs) * 100
                    yg = '{:g}'.format(yg)
                    ll_pd[i] = yg
                alll_pd['次数'] = all_pd
                alll_pd['频数'] = ll_pd
                print(alll_pd)
                print('successful')
            except:
                import traceback

                traceback.print_exc()
                print("Error: unable to fetch data")
            db.close()

            return alll_pd
    else:
        print('sb')

    return render_template('table_01.html')


if __name__ == '__main__':
    app.run()
